package com.lxxie;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.lxxie.enums.Platform;
import com.lxxie.util.DESUtil;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.util.codec.binary.Base64;
import org.springframework.dao.DataAccessException;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.web.client.RestTemplate;

import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by xielx on 21/1/22 9:58
 */
public class Test {
    private final static String DRIVER_CLASS_NAME="oracle.jdbc.driver.OracleDriver";
    private final static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
//    private final static String USER_NAME = "mpp_user_6b";
//    private final static String PASSWORD = "mpp_user_6b";

    //    private final static String USER_NAME = "net_rural";
//    private final static String PASSWORD = "net_rural";
    private final static String USER_NAME = "net_rural_gab";
    private final static String PASSWORD = "net_rural_gab";

    private final static DataSource DATA_SOURCE;
    private final static JdbcTemplate jdbcTemplate;

    static {
        DATA_SOURCE = new DataSource();
        DATA_SOURCE.setUrl(URL);
        DATA_SOURCE.setDriverClassName(DRIVER_CLASS_NAME);
        DATA_SOURCE.setUsername(USER_NAME);
        DATA_SOURCE.setPassword(PASSWORD);
        DATA_SOURCE.setDefaultAutoCommit(true);
        jdbcTemplate = new JdbcTemplate(DATA_SOURCE);
        //黑龙江 海南  西藏
    }

    public static void main(String[] args) throws UnsupportedEncodingException, InterruptedException {
        List<String[]> columns = new ArrayList<>();
        StringBuilder sql1 = new StringBuilder();
        StringBuilder columnSql = new StringBuilder();
        String tableName = "net_rural_jbtz_uncar";
        jdbcTemplate.query("select column_name,data_type from user_tab_columns where table_name=upper('"+tableName+"') order by column_id", new ResultSetExtractor<Object>() {
            @Override
            public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                sql1.append("insert into "+tableName+" (");
                while (resultSet.next()) {
                    String columnName = resultSet.getString(1);
                    sql1.append(columnName).append(",");
                    if("DATE".equals(resultSet.getString(2))){
                        columnSql.append("to_char("+columnName+",'yyyy-mm-dd hh24:mi:ss') "+columnName+",");
                    }else{
                        columnSql.append(columnName+",");
                    }
                    columns.add(new String[]{columnName,resultSet.getString(2)});
                }
                sql1.replace(sql1.length()-1,sql1.length(),")");
                columnSql.replace(columnSql.length()-1,columnSql.length(),"");
                return null;
            }
        });
        RestTemplate restTemplate = new RestTemplate();
        List<String> batchArgs = new ArrayList<>();
        for (int i =0; i < 12; i++) {
            System.err.println("正在执行第"+i+"次的插入");
            batchArgs.clear();
            String tempSql = "select "+columnSql+",rownum rw from "+tableName+" a where isshangb=9 and isshenh=9 and isdel=0 " +
                "and gxsj>=to_date('2021-09-01','yyyy-mm-dd') and chellb in('11','23','27','28')";
            int start = i*1000;
            int end = (i+1)*1000;
            String sql = "select * from ("+tempSql+") t where t.rw<="+end+" and t.rw>"+start;
//            String sql = "select count(1) from "+tableName+" a where isshangb=9 and isshenh=9 and isdel=0 and gxsj>=to_date('2021-09-01','yyyy-mm-dd') and chellb in('11','23','27','28')";
            String encodeSql = new String(Base64.encodeBase64(DESUtil.encrypt(sql.getBytes())));
            JSONObject json = new JSONObject();
            json.put("sfdm", Platform.SFDM_6500.getSfdm());
            json.put("content", encodeSql);
            ResponseEntity<String> resp = restTemplate.postForEntity("http://gab.122.gov.cn/ris/risinfo/queryProvince",
                json.toString().getBytes("UTF-8"), String.class);
            JSONArray result = JSONArray.parseArray(resp.getBody());
            if (result.size()==0) {
                Thread.sleep(1000);
                i--;
                continue;
            }
            JSONObject row = result.getJSONObject(0);
            if (1 != row.getInteger("code")) {
                throw new RuntimeException(row.getString("msg"));
            }
            JSONArray dataList = row.getJSONArray("data");
            for (int i1 = 0; i1 < dataList.size(); i1++) {
                JSONObject data = dataList.getJSONObject(i1);
                StringBuilder isq = new StringBuilder(sql1);
                isq.append(" select ");
                for (int i2 = 0; i2 < columns.size(); i2++) {
                    String[] cl = columns.get(i2);
                    if (data.get(cl[0]) == null||data.getString(cl[0]).length()==0) {
                        isq.append("null,");
                        continue;
                    }
                    if ("DATE".equals(cl[1])) {
                        isq.append(String.format("to_date('%s','yyyy-mm-dd hh24:mi:ss'),",data.getString(cl[0])));
                    }else if("NUMBER".equals(cl[1])){
                        isq.append(data.getInteger(cl[0])).append(",");
                    }else{
                        isq.append("'").append(data.getString(cl[0]).replaceAll("'","''")).append("',");
                    }
                }
                isq.replace(isq.length()-1,isq.length()," from dual where " +
                    "not exists(select 1 from "+tableName+" b where b.bm='"+data.getString("BM")+"')");
                batchArgs.add(isq.toString());
            }
            jdbcTemplate.batchUpdate(batchArgs.toArray(new String[batchArgs.size()]));
//            batchArgs.stream().forEach(temp->{
//                try {
//                    jdbcTemplate.update(temp);
//                }catch (Exception e){
//                    System.err.println(e);
//                    throw e;
//                }
//            });
            jdbcTemplate.execute("commit");
//            jdbcTemplate.execute("rollback");
        }
    }


}
